<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateBasicTables extends \App\Extension\BaseMigration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $this->statement("/**/

            drop table if exists dic ;
            create table if not exists dic(
                dic_name                varchar(32)     not null comment '字典名如：user.status' ,
                dic_key                 varchar(32)     not null comment '值，如：1' ,
                dic_value               varchar(32)     not null comment '含义，如：未激活',
                remark                  varchar(64)     null ,
                primary key(dic_name,dic_key)
            )engine=innodb default charset=utf8 comment '数据字典表';


            drop table if exists organization ;
            create table if not exists organization (
                organization_id         int             not null auto_increment primary key ,
                organization_name       varchar(16)     not null comment '登录账号' ,
                contact_name            varchar(16)     not null comment '联系人' ,
                contact_tel             varchar(16)     null comment '联系电话' ,
                created_by              varchar(32)     null comment '创建人' ,
                created_at              timestamp       not null default '2016-01-01' comment '创建时间' ,
                updated_by              varchar(32)     null comment '最后修改人' ,
                updated_at              timestamp       not null default '2016-01-01' comment '最后修改时间'
            )engine=innodb default charset=utf8 comment '组织机构表';

            drop table if exists admin ;
            create table if not exists admin(
                admin_id                int             not null auto_increment primary key ,
                organization_id         int             not null comment '所属机构,0=书香',
                login_name              varchar(16)     not null comment '登录账号' ,
                user_name               varchar(16)     not null comment '用户姓名' ,
                email                   varchar(128)    null comment '管理员邮箱' ,
                mobile                  varchar(16)     not null comment '手机号码' ,
                password                varchar(16)     not null comment '登录密码' ,
                status_id               tinyint         not null default 1 comment '0=禁用,1=有效' ,
                gender                  tinyint         not null default 0 comment '0=unknow,1=male,2=female' ,
                created_by              varchar(32)     null comment '创建人' ,
                created_at              timestamp       not null default '2016-01-01' comment '创建时间' ,
                updated_by              varchar(32)     null comment '最后修改人' ,
                updated_at              timestamp       not null default '2016-01-01' comment '最后修改时间' ,
                unique index uk_admin_01(login_name) ,
                unique index uk_admin_02(mobile) ,
                index ik_admin_03(status_id)
            )engine=innodb default charset=utf8 comment '管理员信息表';

            drop table if exists menu ;
            create table if not exists menu (
                menu_id           varchar(32)     not null comment '菜单ID' ,
                menu_name         varchar(32)     default null comment '菜单名称',
                url               varchar(64)     default null comment 'url',
                icon              varchar(64)     default null comment '图标',
                parent_id         varchar(32)     default null comment '所属菜单',
                order_number      int(11)         not null default 100 comment '排序',
                public_flag       tinyint         not null default 0 comment '1=公共菜单,2=隐藏菜单' ,
                system_flag       varchar(16)     not null default 'company' comment '所属系统,control=控制系统' ,
                page_file           varchar(128)    null comment '所在的文件' ,
                primary key (menu_id)
            ) engine=innodb default charset=utf8 comment='菜单信息表' ;

           drop table if exists explorer_folder ;
             create table if not exists explorer_folder (
                folder_id           int             not null auto_increment primary key comment '目录ID' ,
                folder_name         varchar(32)     not null comment '文件名称' ,
                parent_id           int             not null default 0 comment '上级目录ID' ,
                created_at          timestamp       not null comment '创建时间' ,
                updated_at          timestamp       not null comment '修改时间' ,
                index ix_explorer_folder_01 (folder_name) ,
                index ix_explorer_folder_02 (parent_id)
            ) engine=innodb default charset=utf8 comment 'explorer目录表';


            drop table if exists explorer_file ;
            create table if not exists explorer_file (
                file_id             int             not null auto_increment primary key comment '文件ID' ,
                folder_id           int             not null comment '所属目录' ,
                file_name           varchar(64)     not null comment '文件名称' ,
                save_name           varchar(256)    not null comment '文件保存相对位置和名称' ,
                file_type           varchar(16)     null comment '文件类型' ,
                file_size           int             not null default 0 comment '文件尺寸' ,
                created_at          timestamp       not null comment '创建时间' ,
                updated_at          timestamp       not null comment '修改时间' ,
                index ix_explorer_file_01 (folder_id)
            ) engine=innodb default charset=utf8 comment 'explorer文件表';

            drop table if exists info ;
            create table if not exists info (
                info_id                 int             not null auto_increment primary key ,
                organization_id         int             not null comment '所属机构,0=书香',
                type_id                 tinyint         not null comment '类型ID,参字典表',
                content                 varchar(5000)   null comment '内容' ,
                sequence                int             not null default 10 comment '排序' ,
                key ix_info_01 (type_id)
            ) engine=innodb default charset=utf8 comment '大段内容表';


            drop table if exists bulletin ;
            create table if not exists bulletin (
                bulletin_id             int             not null auto_increment primary key ,
                subject                 varchar(64)     null comment '公告标题' ,
                brief                   varchar(120)    null comment '内容简要' ,
                organization_id         int             not null comment '所属机构,0=书香',
                type_id                 int             not null comment '所属类型,参字典表' ,
                from_time               datetime        not null comment '发布时间' ,
                to_time                 datetime        not null comment '发布结束时间' ,
                image_file_name         varchar(64)     null comment '图片文件名,图片比例宽：高=3:2' ,
                image_save_name         varchar(200)    null comment '图片保存名' ,
                url                     varchar(200)    null comment '外部内容的URL' ,
                status_id               tinyint         not null default 1 comment '参字典表' ,
                sequence                int             not null default 100 comment '排序' ,
                total_targets           int             not null default 0 comment '目标人员数',
                total_reads             int             not null default 0 comment '总阅读人数,统计字段' ,
                created_id              int             not null comment '创建人ID,admin.admin_id' ,
                created_by              varchar(32)     null comment '创建人' ,
                created_at              timestamp       null comment '创建时间' ,
                updated_by              varchar(32)     null comment '最后修改人' ,
                updated_at              timestamp       null comment '最后修改时间' ,
                key ix_bulletin_01 (type_id) ,
                key ix_bulletin_02 (from_time) ,
                key ix_bulletin_03 (to_time)
            ) engine=innodb default charset=utf8 comment '公告信息表' ;


            drop table if exists bulletin_content ;
            create table if not exists bulletin_content (
                content_id              int             not null auto_increment primary key ,
                bulletin_id             int             not null comment '公告ID',
                content                 varchar(5000)   null comment '公告内容' ,
                sequence                int             not null default 1 comment '排序' ,
                key ix_bulletin_content_01 (bulletin_id)
            ) engine=innodb default charset=utf8 comment '公告内容表';


        ");
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        //
    }
}
